﻿/*
通过设定使用那种数据库,自动编译不同的版本
#define DataAccess   //Oracle.DataAccess.dll
#define ManagedDataAccess //Oracle.ManagedDataAccess.dll
*/
#define ManagedDataAccess //DataAccess || ManagedDataAccess
#if DataAccess
using Oracle.DataAccess.Client;
#else
using Oracle.ManagedDataAccess.Client;
#endif
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Utility.Data.DbClient;
//using System.Data.SQLite;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using System.Data.SQLite;

namespace Utility.Extensions
{
    /// <summary>
    /// 自定义数据库参数类型转换成适配各种数据库的扩展方法
    /// </summary>
    public static class DBParameterExtensions
    {
        /// <summary>
        /// 数据库类型
        /// </summary>
        public enum DatabaseType
        {
            /// <summary>
            /// MS SQLSERVER
            /// </summary>
            MsSqlServer = 0,
            /// <summary>
            /// Oracle
            /// </summary>
            Oracle = 10,
            /// <summary>
            /// SQLite
            /// </summary>
            SQLite = 20,
            /// <summary>
            /// MySql
            /// </summary>
            MySql = 30
        }
        #region SQL转换 
        static readonly IDataParameter[] m_NullParameter = new List<IDataParameter>().ToArray();
        /// <summary>
        /// SQL中匹配参数前缀字符 表达式
        /// </summary>
        static readonly string m_SqlRegexPattern = "";
        /// <summary>
        /// 将指定SQL字符串转换成目标SQL类型
        /// (用于SQL中参数前缀符号转换,如:将oracle通过此扩展方法转换成MsSql SQL语句)
        /// </summary>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        public static string ToSql(this string value, DatabaseType type = DatabaseType.Oracle, char prefixChar = ':')
        {
            // 想法: 想通过正则表达式找出SQL中真正是参数的前缀字符,进行替换(正则表达式没写对,故暂时使用replace进行替换)
            //Regex reg = null;
            switch (type)
            {
                case DatabaseType.Oracle:
                    if (c_ORACLE_PREFIX_CHAR != prefixChar)
                    {
                        //reg = new Regex(m_SqlRegexPattern.Replace("{0}", prefixChar.ToString()), RegexOptions.Compiled);
                        //value = reg.Replace(value, c_ORACLE_PREFIX_CHAR.ToString());
                        value = value.Replace(prefixChar, c_ORACLE_PREFIX_CHAR);
                    }
                    break;
                case DatabaseType.MsSqlServer:
                    if (c_MSSQL_PREFIX_CHAR != prefixChar)
                    {
                        value = value.Replace(prefixChar, c_MSSQL_PREFIX_CHAR);
                    }
                    break;
                case DatabaseType.SQLite:
                    if (c_SQLITE_PREFIX_CHAR != prefixChar)
                    {
                        value = value.Replace(prefixChar, c_SQLITE_PREFIX_CHAR);
                    }
                    break;
                case DatabaseType.MySql:
                    if (c_MYSQL_PREFIX_CHAR != prefixChar)
                    {
                        value = value.Replace(prefixChar, c_MYSQL_PREFIX_CHAR);
                    }
                    break;
            }
            return value;
        }
        #endregion
        /// <summary>
        /// 转换成指定数据类型数据
        /// </summary>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        public static IDataParameter ToDataParameter(this DBParameter value, DatabaseType type = DatabaseType.Oracle, char prefixChar = ':')
        {
            switch (type)
            {
                case DatabaseType.Oracle:
                    return value.ToOracleParameter(prefixChar);
                case DatabaseType.MsSqlServer:
                    return value.ToMsSqlParameter(prefixChar);
                //case DatabaseType.SQLite:
                //    return value.ToSQLiteParameter(prefixChar);
                case DatabaseType.MySql:
                    return value.ToMySqlParameter(prefixChar);
                default:
                    return null;
            }
        }
        /// <summary>
        /// 转换成指定数据类型数据
        /// </summary>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        public static IDataParameter[] ToDataParameter(this DBParameter[] value, DatabaseType type = DatabaseType.Oracle, char prefixChar = ':')
        {
            switch (type)
            {
                case DatabaseType.Oracle:
                    return value.ToOracleParameter(prefixChar);
                case DatabaseType.MsSqlServer:
                    return value.ToMsSqlParameter(prefixChar);
                case DatabaseType.SQLite:
                    return value.ToSQLiteParameter(prefixChar);
                case DatabaseType.MySql:
                    return value.ToMySqlParameter(prefixChar);
                default:
                    return null;
            }
        }
        /// <summary>
        /// 转换成指定数据类型数据
        /// </summary>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        public static IDataParameter[] ToDataParameter(this List<DBParameter> value, DatabaseType type = DatabaseType.Oracle, char prefixChar = ':')
        {
            return ToDataParameter(value.ToArray(), type, prefixChar);
        }

        #region Oracle
        /// <summary>
        /// Oracle参数变量前缀
        /// </summary>
        static char c_ORACLE_PREFIX_CHAR = ':';
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        private static IDataParameter ToOracleParameter(this DBParameter value, char prefixChar = ':')
        {
            if (null == value)
            {
                return null;
            }
            if (c_ORACLE_PREFIX_CHAR != prefixChar)
            {
                value.ParameterName = value.ParameterName.Replace(prefixChar, c_ORACLE_PREFIX_CHAR);
            }
            OracleParameter parameter = new OracleParameter(value.ParameterName, value.Value);
            parameter.Direction = value.Direction;
            if (null != value.DbType)
            {
                parameter.OracleDbType = (OracleDbType)ConvertDBParameterType((DBType)value.DbType, DatabaseType.Oracle);
            }
            if (null != value.Size)
            {
                parameter.Size = (int)value.Size;
            }
            return parameter;
        }
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        private static IDataParameter[] ToOracleParameter(this DBParameter[] value, char prefixChar = ':')
        {
            if (null == value || value.Length == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToOracleParameter(prefixChar);
            }).ToArray();
        }
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        private static IDataParameter[] ToOracleParameter(this List<DBParameter> value, char prefixChar = ':')
        {
            if (null == value || value.Count == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToOracleParameter(prefixChar);
            }).ToArray();
        }
        #endregion

        #region MsSqlServer
        /// <summary>
        /// MsSql 参数变量前缀
        /// </summary>
        static char c_MSSQL_PREFIX_CHAR = '@';
        /// <summary>
        /// 转换成MsSql参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 @ </param>
        /// <returns></returns>
        private static IDataParameter ToMsSqlParameter(this DBParameter value, char prefixChar = '@')
        {
            if (null == value)
            {
                return null;
            }
            if (c_MSSQL_PREFIX_CHAR != prefixChar)
            {
                value.ParameterName = value.ParameterName.Replace(prefixChar, c_MSSQL_PREFIX_CHAR);
            }
            SqlParameter parameter = new SqlParameter(value.ParameterName, value.Value);
            parameter.Direction = value.Direction;
            if (null != value.DbType)
            {
                parameter.SqlDbType = (SqlDbType)ConvertDBParameterType((DBType)value.DbType, DatabaseType.MsSqlServer);
            }
            if (null != value.Size)
            {
                parameter.Size = (int)value.Size;
            }
            return parameter;
        }
        /// <summary>
        /// 转换成MsSql参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 @ </param>
        /// <returns></returns>
        private static IDataParameter[] ToMsSqlParameter(this DBParameter[] value, char prefixChar = '@')
        {
            if (null == value || value.Length == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToMsSqlParameter(prefixChar);
            }).ToArray();
        }
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 @ </param>
        /// <returns></returns>
        private static IDataParameter[] ToMsSqlParameter(this List<DBParameter> value, char prefixChar = '@')
        {
            if (null == value || value.Count == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToMsSqlParameter(prefixChar);
            }).ToArray();
        }
        #endregion

        #region SQLite
        /// <summary>
        /// Sqlite 参数变量前缀
        /// </summary>
        static char c_SQLITE_PREFIX_CHAR = '@';
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 @ </param>
        /// <returns></returns>
        private static IDataParameter ToSQLiteParameter(this DBParameter value, char prefixChar = '@')
        {
            if (null == value)
            {
                return null;
            }
            if (c_SQLITE_PREFIX_CHAR != prefixChar)
            {
                value.ParameterName = value.ParameterName.Replace(prefixChar, c_SQLITE_PREFIX_CHAR);
            }
            SQLiteParameter parameter = new SQLiteParameter(value.ParameterName, value.Value);
            parameter.Direction = value.Direction;
            if (null != value.DbType)
            {
                parameter.DbType = (DbType)ConvertDBParameterType((DBType)value.DbType, DatabaseType.SQLite);
            }
            if (null != value.Size)
            {
                parameter.Size = (int)value.Size;
            }
            return parameter;
        }
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 @ </param>
        /// <returns></returns>
        private static IDataParameter[] ToSQLiteParameter(this DBParameter[] value, char prefixChar = '@')
        {
            if (null == value || value.Length == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToSQLiteParameter(prefixChar);
            }).ToArray();
        }
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 @ </param>
        /// <returns></returns>
        private static IDataParameter[] ToSQLiteParameter(this List<DBParameter> value, char prefixChar = '@')
        {
            if (null == value || value.Count == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToSQLiteParameter(prefixChar);
            }).ToArray();
        }
        #endregion

        #region MySql
        /// <summary>
        /// Oracle参数变量前缀
        /// </summary>
        static char c_MYSQL_PREFIX_CHAR = '?';
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        private static IDataParameter ToMySqlParameter(this DBParameter value, char prefixChar = '?')
        {
            if (null == value)
            {
                return null;
            }
            if (c_MYSQL_PREFIX_CHAR != prefixChar)
            {
                value.ParameterName = value.ParameterName.Replace(prefixChar, c_MYSQL_PREFIX_CHAR);
            }
            MySqlParameter parameter = new MySqlParameter(value.ParameterName, value.Value);
            parameter.Direction = value.Direction;
            if (null != value.DbType)
            {
                parameter.MySqlDbType = (MySqlDbType)ConvertDBParameterType((DBType)value.DbType, DatabaseType.MySql);
            }
            if (null != value.Size)
            {
                parameter.Size = (int)value.Size;
            }
            return parameter;
        }
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        private static IDataParameter[] ToMySqlParameter(this DBParameter[] value, char prefixChar = '?')
        {
            if (null == value || value.Length == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToMySqlParameter(prefixChar);
            }).ToArray();
        }
        /// <summary>
        /// 转换成Oracle参数
        /// </summary>
        /// <param name="value"></param>
        /// <param name="prefixChar">参数变量前缀,默认 : </param>
        /// <returns></returns>
        private static IDataParameter[] ToMySqlParameter(this List<DBParameter> value, char prefixChar = '?')
        {
            if (null == value || value.Count == 0)
            {
                return m_NullParameter;
            }
            return value.Select(t =>
            {
                return t.ToMySqlParameter(prefixChar);
            }).ToArray();
        }
        #endregion

        /// <summary>
        /// 数据类型转换
        /// 
        /// </summary>
        /// <param name="dbParameterType">DB参数类型</param>
        /// <param name="convertDbType">目标类型</param>
        /// <returns>转换成int 再转枚举</returns>
        private static int ConvertDBParameterType(DBType dbParameterType, DatabaseType convertDbType = DatabaseType.Oracle)
        {
            string type = dbParameterType.ToString();
            int value = (int)dbParameterType;
            if (type.IndexOf("Oracle_", StringComparison.OrdinalIgnoreCase) == 0)
            {
                if (convertDbType == DatabaseType.Oracle)
                {
                    return value % 1000;
                }
                #region Oracle
                switch (dbParameterType)
                {
                    case DBType.Oracle_Object:
                    case DBType.Oracle_BFile:
                        {
                            switch (convertDbType)
                            {
                                case DatabaseType.MsSqlServer:
                                    value = (int)DBType.MsSql_Image;
                                    break;
                                case DatabaseType.SQLite:
                                    value = (int)DBType.SQLite_Object;
                                    break;
                                case DatabaseType.MySql:
                                    value = (int)DBType.MySql_String;
                                    break;
                            }
                        }
                        break;
                    case DBType.Oracle_Blob:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Text;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Text;
                                break;
                        }
                        break;
                    case DBType.Oracle_Char:
                    case DBType.Oracle_Byte:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Char;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_VarChar;
                                break;
                        }
                        break;
                    case DBType.Oracle_LongRaw:
                    case DBType.Oracle_NClob:
                    case DBType.Oracle_Clob:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Text;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.Oracle_Date:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_DateTime;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_DateTime;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MsSql_DateTime;
                                break;
                        }
                        break;
                    case DBType.Oracle_Double:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Float;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Double;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Float;
                                break;
                        }
                        break;
                    case DBType.Oracle_Decimal:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Decimal;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Decimal;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Decimal;
                                break;
                        }
                        break;
                    case DBType.Oracle_Long:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_BigInt;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int64;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int64;
                                break;
                        }
                        break;
                    case DBType.Oracle_Int16:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int16;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int16;
                                break;
                        }
                        break;
                    case DBType.Oracle_Int32:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int32;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int32;
                                break;
                        }
                        break;
                    case DBType.Oracle_Int64:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_BigInt;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int64;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int64;
                                break;
                        }
                        break;
                    case DBType.Oracle_NChar:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_NChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_VarChar;
                                break;
                        }
                        break;
                    case DBType.Oracle_NVarchar2:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_NVarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.Oracle_Varchar2:
                    case DBType.Oracle_Raw:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_VarChar;
                                break;
                        }
                        break;
                    case DBType.MsSql_Timestamp:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_TimeStamp;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_DateTime;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Timestamp;
                                break;
                        }
                        break;
                    case DBType.Oracle_Array:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Binary;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Binary;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Binary;
                                break;
                        }
                        break;
                    case DBType.Oracle_IntervalDS:
                    case DBType.Oracle_IntervalYM:
                    case DBType.Oracle_RefCursor:
                    case DBType.Oracle_Single:
                    case DBType.Oracle_XmlType:
                    case DBType.Oracle_Ref:
                    case DBType.Oracle_BinaryDouble:
                    case DBType.Oracle_BinaryFloat:
                        switch (convertDbType)
                        {
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Binary;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Binary;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Binary;
                                break;
                        }
                        break;
                }
                #endregion
            }
            else if (type.IndexOf("MsSql_", StringComparison.OrdinalIgnoreCase) == 0)
            {
                if (convertDbType == DatabaseType.MsSqlServer)
                {
                    return value % 1000;
                }
                #region MsSql
                switch (dbParameterType)
                {
                    case DBType.MsSql_NVarChar:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_NVarchar2;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.MsSql_VarChar:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.MsSql_NChar:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_NChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.MsSql_Image:
                        {
                            switch (convertDbType)
                            {
                                case DatabaseType.Oracle:
                                    value = (int)DBType.Oracle_Object;
                                    break;
                                case DatabaseType.SQLite:
                                    value = (int)DBType.SQLite_Object;
                                    break;
                                case DatabaseType.MySql:
                                    value = (int)DBType.MySql_String;
                                    break;
                            }
                        }
                        break;
                    case DBType.MsSql_Char:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Char;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_VarChar;
                                break;
                        }
                        break;
                    case DBType.MsSql_Text:
                    case DBType.MsSql_NText:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Clob;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Text;
                                break;
                        }
                        break;
                    case DBType.MsSql_Xml:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_XmlType;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Xml;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Text;
                                break;
                        }
                        break;
                    case DBType.MsSql_Time:
                    case DBType.MsSql_Date:
                    case DBType.MsSql_DateTime:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_DateTime;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_DateTime;
                                break;
                        }
                        break;
                    case DBType.MsSql_DateTime2:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_DateTime2;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_DateTime;
                                break;
                        }
                        break;
                    case DBType.MsSql_DateTimeOffset:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_TimeStamp;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_DateTimeOffset;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Time;
                                break;
                        }
                        break;
                    case DBType.MsSql_Decimal:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Decimal;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Double;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Decimal;
                                break;
                        }
                        break;
                    case DBType.MsSql_Float:
                    case DBType.MsSql_Money:
                    case DBType.MsSql_Real:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Decimal;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Decimal;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Float;
                                break;
                        }
                        break;
                    case DBType.MsSql_BigInt:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Long;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int64;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int64;
                                break;
                        }
                        break;
                    case DBType.MsSql_Int:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int32;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int32;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int32;
                                break;
                        }
                        break;
                    case DBType.MsSql_Binary:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Array;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Binary;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Binary;
                                break;
                        }
                        break;
                    case DBType.MsSql_Bit:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Char;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Boolean;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Bit;
                                break;
                        }
                        break;
                }

                #endregion
            }
            else if (type.IndexOf("SQLite_", StringComparison.OrdinalIgnoreCase) == 0)
            {
                if (convertDbType == DatabaseType.SQLite)
                {
                    return value % 1000;
                }
                #region SQLite
                switch (dbParameterType)
                {
                    case DBType.SQLite_AnsiString:
                    case DBType.SQLite_AnsiStringFixedLength:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_VarChar;
                                break;
                        }
                        break;
                    case DBType.SQLite_Binary:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Array;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Binary;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Binary;
                                break;
                        }
                        break;
                    case DBType.SQLite_Boolean:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Char;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Bit;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Bit;
                                break;
                        }
                        break;
                    case DBType.SQLite_Byte:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Byte;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Char;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Byte;
                                break;
                        }
                        break;
                    case DBType.SQLite_Currency:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Decimal;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Money;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Decimal;
                                break;
                        }
                        break;
                    case DBType.SQLite_Date:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Date;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Date;
                                break;
                        }
                        break;
                    case DBType.SQLite_DateTime:
                    case DBType.SQLite_Time:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_DateTime;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Time;
                                break;
                        }
                        break;
                    case DBType.SQLite_DateTime2:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_DateTime2;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_DateTime;
                                break;
                        }
                        break;
                    case DBType.SQLite_DateTimeOffset:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_TimeStamp;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_DateTimeOffset;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_DateTime;
                                break;
                        }
                        break;
                    case DBType.SQLite_Decimal:
                    case DBType.SQLite_Double:
                    case DBType.SQLite_VarNumeric:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Decimal;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Decimal;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Double;
                                break;
                        }
                        break;
                    case DBType.SQLite_Guid:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_UniqueIdentifier;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Guid;
                                break;
                        }
                        break;
                    case DBType.SQLite_Int16:
                    case DBType.SQLite_UInt16:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int16;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int16;
                                break;
                        }
                        break;
                    case DBType.SQLite_Int32:
                    case DBType.SQLite_UInt32:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int32;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int32;
                                break;
                        }
                        break;
                    case DBType.SQLite_Int64:
                    case DBType.SQLite_UInt64:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int64;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_BigInt;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Int64;
                                break;
                        }
                        break;
                    case DBType.SQLite_Object:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Object;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Image;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.SQLite_SByte:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Byte;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Char;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_Byte;
                                break;
                        }
                        break;
                    case DBType.SQLite_Single:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Single;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.SQLite_String:
                    case DBType.SQLite_StringFixedLength:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                    case DBType.SQLite_Xml:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_XmlType;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Xml;
                                break;
                            case DatabaseType.MySql:
                                value = (int)DBType.MySql_String;
                                break;
                        }
                        break;
                }
                #endregion
            }
            else if (type.IndexOf("MySql_", StringComparison.OrdinalIgnoreCase) == 0)
            {
                if (convertDbType == DatabaseType.MySql)
                {
                    return value % 1000;
                }
                #region MySql
                switch (dbParameterType)
                {
                    case DBType.MySql_Bit:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Byte;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Bit;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Byte;
                                break;
                        }
                        break;
                    case DBType.MySql_Binary:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_BinaryDouble;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Binary;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Binary;
                                break;
                        }
                        break;
                    case DBType.MySql_Blob:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Blob;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_Byte:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Byte;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Byte;
                                break;
                        }
                        break;
                    case DBType.MySql_Date:
                    case DBType.MySql_DateTime:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Date;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_DateTime;
                                break;
                        }
                        break;
                    case DBType.MySql_Decimal:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Decimal;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Decimal;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Decimal;
                                break;
                        }
                        break;
                    case DBType.MySql_Enum:
                    case DBType.MySql_Float:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Double;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Float;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Double;
                                break;
                        }
                        break;
                    case DBType.MySql_Geometry:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_Guid:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Guid;
                                break;
                        }
                        break;
                    case DBType.MySql_Int16:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int16;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int16;
                                break;
                        }
                        break;
                    case DBType.MySql_Int24:
                    case DBType.MySql_Int32:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int32;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int32;
                                break;
                        }
                        break;
                    case DBType.MySql_Int64:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int64;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Int64;
                                break;
                        }
                        break;
                    case DBType.MySql_JSON:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_LongBlob:
                    case DBType.MySql_LongText:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Long;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_MediumBlob:
                    case DBType.MySql_MediumText:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_Newdate:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Date;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Date;
                                break;
                        }
                        break;
                    case DBType.MySql_NewDecimal:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Decimal;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Decimal;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Decimal;
                                break;
                        }
                        break;
                    case DBType.MySql_Set:
                    case DBType.MySql_String:
                    case DBType.MySql_Text:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_Time:
                    case DBType.MySql_Timestamp:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_TimeStamp;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Time;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Time;
                                break;
                        }
                        break;
                    case DBType.MySql_TinyBlob:
                    case DBType.MySql_TinyText:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_TinyInt;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_UByte:
                    case DBType.MySql_UInt16:
                    case DBType.MySql_UInt24:
                    case DBType.MySql_UInt32:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int64;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_UInt32;
                                break;
                        }
                        break;
                    case DBType.MySql_UInt64:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Int64;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Int;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_UInt64;
                                break;
                        }
                        break;
                    case DBType.MySql_VarBinary:
                    case DBType.MySql_VarChar:
                    case DBType.MySql_VarString:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Varchar2;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_VarChar;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_String;
                                break;
                        }
                        break;
                    case DBType.MySql_Year:
                        switch (convertDbType)
                        {
                            case DatabaseType.Oracle:
                                value = (int)DBType.Oracle_Date;
                                break;
                            case DatabaseType.MsSqlServer:
                                value = (int)DBType.MsSql_Date;
                                break;
                            case DatabaseType.SQLite:
                                value = (int)DBType.SQLite_Date;
                                break;
                        }
                        break;
                }
                #endregion
            }
            return value % 1000;
        }
    }
}
